﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Dapper;
using System.Linq;
using Gx.Model.Entity;//实体
using Gx.Common;//通用类

namespace Gx.Dal.Manage
{
    /// <summary>
    /// 级别数据层
    /// </summary>
    public class LevelInfoDal : SqlServerHelper, IDisposable
    {
        public List<E_LevelInfo> GetLevelInfoList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT");
            strSql.Append(" Id");
            strSql.Append(",LevelName");
            strSql.Append(",Status");
            strSql.AppendFormat(" FROM E_LevelInfo WHERE 1=1 {0}", strWhere);
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<E_LevelInfo> list = conn.Query<E_LevelInfo>(strSql.ToString()).ToList();
                return list;
            }
        }

        public int AddLevelInfo(E_LevelInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO E_LevelInfo");
            strSql.Append("(");
            strSql.Append("LevelName,");
            strSql.Append("Status,");
            strSql.Append("ParentID");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@LevelName,");
            strSql.Append("@Status,");
            strSql.Append("@ParentID");
            strSql.Append(");SELECT @@IDENTITY;");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                object obj = conn.ExecuteScalar(strSql.ToString(), model);
                return obj.IntParse();
            }
        }

        public bool DelLevelInfo(int Id)
        {
            string strSql = @"
            DELETE FROM dbo.E_LevelInfo WHERE Id=@Id;
            DELETE FROM dbo.E_DepLevelInfo WHERE LevelId=@Id;";
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), new { Id }) > 0;
            }
        }

        public bool EditLevelInfo(E_LevelInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE E_LevelInfo SET ");
            strSql.Append(" LevelName=@LevelName,");
            strSql.Append(" ParentID=@ParentID");
            strSql.Append(" WHERE Id=@Id");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), model) > 0;
            }
        }

        public List<E_LevelInfo> GetLevelInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            totalCount = GetLevelInfoCount(strWhere);
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS ORDERBYID");
            strSql.Append(",Id");
            strSql.Append(",LevelName");
            strSql.Append(",Status");
            strSql.Append(",ParentID");
            strSql.AppendFormat(" FROM E_LevelInfo WHERE 1=1 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<E_LevelInfo> list = conn.Query<E_LevelInfo>(strSql.ToString()).ToList();
                return list;
            }
        }

        public int GetLevelInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM E_LevelInfo WHERE [Status]=0  {0}", strWhere);
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings)) { object obj = conn.ExecuteScalar(strSql.ToString()); return obj.IntParse(); };
        }


        public E_LevelInfo GetE_LevelInfoModel(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("Id");
            strSql.Append(",LevelName");
            strSql.Append(",ParentID");
            strSql.Append(",Status");
            strSql.Append(" FROM E_LevelInfo WITH(NOLOCK) WHERE Id=@Id");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                E_LevelInfo model = conn.Query<E_LevelInfo>(strSql.ToString(), new { Id }).SingleOrDefault();
                return model;
            }
        }
    }
}
